﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;
using System.ComponentModel;
using System.Collections;

    [DataObject(true)]
    public class AdminReport
    {

        private const string CONNECTION_STRING_NAME = "ApplicationConnectionString";

        private const string GET_CREATED_USERS = "SELECT u.user_id AS 'user id', u.username, u.first_name AS 'first name', u.last_name AS 'last name', u.creation_date AS 'date created', u2.username as 'created by' "
                                               + "FROM users u, users u2 "
                                               + "WHERE u.creation_date >= @minDate "
                                               + "AND u.creation_date <= @maxDate "
                                               + "AND u.created_user_id = u2.user_id "
                                               + "ORDER BY  u.creation_date ";

        private const string GET_CREATED_MENTORS = "SELECT m.mentor_id AS 'mentor id', m.first_name AS 'first name' , m.last_name AS 'last', m.creation_date AS 'date created', u.username as 'created_by' "
                                                 + "FROM mentor m, "
                                                 + "users u "
                                                 + "WHERE m.creation_date >= @minDate "
                                                 + "AND m.creation_date <= @maxDate "
                                                 + "AND m.creation_user_id = u.user_id "
                                                 + "ORDER BY  m.creation_date ";

        private const string GET_DELETED_MENTORS = "SELECT m.mentor_id AS 'mentor id', m.first_name AS 'first  name' , m.last_name AS 'last name', m.creation_date AS 'date created', u.username as 'deleted_by' "
                                                 + "FROM mentor m, "
                                                 + "users u "
                                                 + "WHERE m.deletion_date >= @minDate "
                                                 + "AND m.deletion_date <= @maxDate "
                                                 + "AND m.deletion_user_id = u.user_id "
                                                 + "ORDER BY  m.deletion_date ";
        public string getCreatedUsersSQL()
        {
            return GET_CREATED_USERS;
        }
        public string getCreatedMentorsSQL()
        {
            return GET_CREATED_MENTORS;
        }
        public string getDeletedMentorsSQL()
        {
            return GET_DELETED_MENTORS;
        }

        
        private static string getConnectionString()
        {
            return ConfigurationManager.ConnectionStrings[CONNECTION_STRING_NAME].ConnectionString;
        }

    }
